#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
SQL Server 简化连接测试脚本
专门用于测试最基本的连接配置
"""

import pyodbc
import sys
import os
from datetime import datetime

def print_header():
    """打印测试头部信息"""
    print("=" * 50)
    print("🔧 SQL Server 简化连接测试")
    print("=" * 50)
    print(f"📅 测试时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print("=" * 50)

def test_basic_connections():
    """测试基本连接配置"""
    print("🔗 测试基本连接配置...")
    
    # 最简单的连接字符串 - 从最宽松到最严格
    conn_strings = [
        # 1. 最基本连接 - 无SSL参数
        {
            "name": "基本连接(无SSL参数)",
            "conn_str": "DRIVER={ODBC Driver 17 for SQL Server};SERVER=fast9.shenzhuo.vip,56472;DATABASE=GP;UID=sa;PWD=shanks00;"
        },
        
        # 2. 明确禁用加密
        {
            "name": "禁用加密(Encrypt=No)",
            "conn_str": "DRIVER={ODBC Driver 17 for SQL Server};SERVER=fast9.shenzhuo.vip,56472;DATABASE=GP;UID=sa;PWD=shanks00;Encrypt=No;"
        },
        
        # 3. 禁用加密 + 信任证书
        {
            "name": "禁用加密+信任证书",
            "conn_str": "DRIVER={ODBC Driver 17 for SQL Server};SERVER=fast9.shenzhuo.vip,56472;DATABASE=GP;UID=sa;PWD=shanks00;TrustServerCertificate=Yes;Encrypt=No;"
        },
        
        # 4. 使用IP地址
        {
            "name": "使用IP地址",
            "conn_str": "DRIVER={ODBC Driver 17 for SQL Server};SERVER=115.231.231.115,56472;DATABASE=GP;UID=sa;PWD=shanks00;Encrypt=No;"
        },
        
        # 5. 强制TLS 1.0
        {
            "name": "强制TLS 1.0",
            "conn_str": "DRIVER={ODBC Driver 17 for SQL Server};SERVER=fast9.shenzhuo.vip,56472;DATABASE=GP;UID=sa;PWD=shanks00;Encrypt=No;TLS_Version=1.0;"
        },
        
        # 6. 强制TLS 1.2
        {
            "name": "强制TLS 1.2", 
            "conn_str": "DRIVER={ODBC Driver 17 for SQL Server};SERVER=fast9.shenzhuo.vip,56472;DATABASE=GP;UID=sa;PWD=shanks00;Encrypt=No;TLS_Version=1.2;"
        },
        
        # 7. 完全信任模式
        {
            "name": "完全信任模式",
            "conn_str": "DRIVER={ODBC Driver 17 for SQL Server};SERVER=fast9.shenzhuo.vip,56472;DATABASE=GP;UID=sa;PWD=shanks00;Encrypt=No;TrustServerCertificate=Yes;Trusted_Connection=No;"
        },
        
        # 8. 兼容模式
        {
            "name": "兼容模式",
            "conn_str": "DRIVER={ODBC Driver 17 for SQL Server};SERVER=fast9.shenzhuo.vip,56472;DATABASE=GP;UID=sa;PWD=shanks00;Encrypt=No;TrustServerCertificate=Yes;MultiSubnetFailover=No;"
        }
    ]
    
    success_count = 0
    
    for i, config in enumerate(conn_strings, 1):
        print(f"\n   🔄 测试方案 {i}: {config['name']}")
        print(f"      连接字符串: {config['conn_str']}")
        
        try:
            # 设置较短的超时时间快速失败
            conn = pyodbc.connect(config['conn_str'], timeout=15)
            print("      ✅ 连接成功!")
            
            # 执行简单查询验证连接
            cursor = conn.cursor()
            cursor.execute("SELECT @@VERSION")
            result = cursor.fetchone()
            print(f"      📊 SQL Server版本: {result[0][:50]}...")
            
            # 测试基本查询
            try:
                cursor.execute("SELECT GETDATE() as current_time")
                time_result = cursor.fetchone()
                print(f"      ⏰ 服务器时间: {time_result[0]}")
            except Exception as query_e:
                print(f"      ⚠️  查询测试失败: {query_e}")
            
            conn.close()
            print(f"      🎉 方案 {i} 完全成功!")
            success_count += 1
            
            # 如果找到可用连接，记录下来
            print(f"\n✨ 找到可用的连接配置!")
            print(f"推荐使用: {config['conn_str']}")
            break
            
        except Exception as e:
            error_str = str(e)
            print(f"      ❌ 连接失败: {error_str}")
            
            # 分析具体错误类型
            if "handshakes before login" in error_str:
                print("      💡 提示: TLS/SSL握手失败")
            elif "Login timeout expired" in error_str:
                print("      💡 提示: 连接超时，可能是网络问题")
            elif "Invalid value" in error_str:
                print("      💡 提示: 连接参数无效")
            elif "Can't open lib" in error_str:
                print("      💡 提示: 驱动程序未找到")
    
    return success_count

def test_environment():
    """测试环境信息"""
    print("\n🖥️  环境检查:")
    
    # 检查ODBC驱动
    try:
        drivers = pyodbc.drivers()
        print(f"   📦 可用ODBC驱动 ({len(drivers)}个):")
        for driver in drivers:
            print(f"      - {driver}")
    except Exception as e:
        print(f"   ❌ 无法获取驱动列表: {e}")
    
    # 检查环境变量
    ssl_vars = ['OPENSSL_CONF', 'MSSQL_CLI_TELEMETRY_OPTOUT', 'TLS_VERSION']
    print(f"\n   🔧 SSL相关环境变量:")
    for var in ssl_vars:
        value = os.environ.get(var)
        if value:
            print(f"      {var}={value}")
        else:
            print(f"      {var}=未设置")

def apply_ssl_fixes():
    """应用SSL修复建议"""
    print("\n🔧 SSL修复建议:")
    print("   如果所有连接都失败，请尝试以下命令:")
    print()
    print("   1. 降低OpenSSL安全级别:")
    print("      sudo tee -a /etc/ssl/openssl.cnf << 'EOF'")
    print("      [system_default_sect]")
    print("      MinProtocol = TLSv1.0")
    print("      CipherString = DEFAULT:@SECLEVEL=1")
    print("      EOF")
    print()
    print("   2. 设置环境变量:")
    print("      export OPENSSL_CONF=/etc/ssl/openssl.cnf")
    print("      export MSSQL_CLI_TELEMETRY_OPTOUT=1")
    print()
    print("   3. 重新运行测试:")
    print("      python3 simple_test.py")

def main():
    """主函数"""
    print_header()
    
    # 环境检查
    test_environment()
    
    # 连接测试
    success_count = test_basic_connections()
    
    # 结果总结
    print("\n" + "=" * 50)
    print("📋 测试结果:")
    print("=" * 50)
    
    if success_count > 0:
        print(f"🎉 成功! 找到 {success_count} 个可用的连接配置")
        print("   你的应用应该能正常连接数据库了!")
        return 0
    else:
        print("❌ 所有连接测试都失败了")
        apply_ssl_fixes()
        return 1

if __name__ == "__main__":
    try:
        exit_code = main()
        sys.exit(exit_code)
    except KeyboardInterrupt:
        print("\n\n⏹️  测试被用户中断")
        sys.exit(1)
    except Exception as e:
        print(f"\n\n💥 测试过程中发生错误: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)